const express = require('express')
const path = require('path')
const models = require(path.join(__dirname,'../db/db'));

const router = express.Router()
const mysql = require('mysql');
const sql = require(path.join(__dirname,'../db/sqlMap'));

const conn = mysql.createConnection(models.mysql);
conn.connect();

// 查询所有
router.get("/getAllAnnouncements" , (req , res) => {
  let allTotal//总条数
  let sel_all = sql.announcements.selectAll

  let key = 0
  // 前端传过来的查询条件和页码数
  // 判断是否有查询条件
  if(req.query.title || req.query.type){
    sel_all += ' where '
    if(req.query.title){
      sel_all += 'title = \'' + req.query.title + '\''
      key = 1
    }
    if(req.query.type){
      if(key == '1'){
        sel_all += 'and '
      }
      sel_all += 'type = \'' + req.query.type + '\''
    }
  }
  

  conn.query(sel_all,(error,results)=>{
    if(error){
      return error
    }
    // 总条数赋值
    allTotal = results.length
  })

  // 查询的sql语句
  sel_all += ' order by id desc limit ' + Number(req.query.number-1) * Number(req.query.size) + ',' + Number(req.query.size)
  conn.query(sel_all,(error,results)=>{
    if(error){
      return error
    }
    let dataObj = {
      data:results,
      total:allTotal
    }
    res.send(JSON.stringify(dataObj))
  })

})
// 条件查询
// router.get("/getConditionAnnouncements" , (req , res) => {
//   let key = 0
//   const announcements = req.query
//   let sel_all = sql.announcements.selectAll + ' where '
//   if(announcements.title){
//     sel_all += 'title = \'' + announcements.title + '\''
//     key = 1
//   }
//   if(announcements.type){
//     if(key == '1'){
//       sel_all += 'and '
//     }
//     sel_all += 'type = \'' + announcements.type + '\''
//   }
//   conn.query(sel_all,(error,rst)=>{
//     if(error){
//       return error
//     }
//     res.send(JSON.stringify(rst))//这里就是返回的数据，得加到条件里
//   })
  
// })
// 新增保存
router.post('/addAnnouncements' , (req , res) => {
  const announcements = req.query 
  let addSave = sql.announcements.addSave

  conn.query(addSave , 
    [announcements.title,announcements.type,announcements.announceStatus,announcements.person,announcements.publish,announcements.textarea,announcements.date],
    (error,rst)=>{
      if(error){
        console.log(error);
        return error
      }
      res.send('success')
  })
})
// 编辑保存
router.post('/editAnnouncements' , (req , res) => {
  const announcements = req.query
  let editSave = sql.announcements.edit

  // 需要处理日期的格式
  if(announcements.date){
    let datetime = new Date(announcements.date)
    let Y = datetime.getFullYear() + '-'
    let M = (datetime.getMonth() + 1 < 10 ? ('0' + (datetime.getMonth() + 1)) : datetime.getMonth() + 1) + '-'
    let D = (datetime.getDate() + 1 < 10 ? '0' + datetime.getDate() : datetime.getDate()) + ''
    announcements.date = Y + M + D
  }

  conn.query(editSave , 
    [announcements.title,announcements.type,announcements.announceStatus,announcements.person,announcements.publish,announcements.textarea,announcements.date,announcements.id],
    (error , rst) => {
      if(error){
        return error
      }

      res.send('success')
  })
})
// 删除
router.get('/delAnnouncements' , (req , res) => {
  const announcements = req.query
  let del_sql = sql.announcements.delete + ' where id = \'' + announcements.id + '\'' 
  conn.query(del_sql , (error , results) => {
    if(error){
      return error
    }
    res.send("success")
  })
})

module.exports = router